﻿<cfscript>
/** 
* @hint "acadmicBase - Subject"
*/ 

component 
	extends="MachII.framework.Listener" 
	displayname="acadmicManager.listeners.senate.subjectListener" 
	output=false 
	depends="markAdvice, creditFactory, sequenceSubject" 
	{
	
	/**
	* @hint "Configures this listener as part of the Mach-II framework"
	*/
	public void function configure() output=false {} 
	

	/**
	* @hint "刷新专业年级成绩统计信息"
	* 
	* @event "MachII Event Object"
	*/
	public void function flushSubjectGradeMarkProfile( required MachII.framework.Event event ) output=false {
		
		var subjectId = event.getArg("SubID");
		var grade = event.getArg("Grade");
		var markAdvice = getmarkAdvice();
		
		var sql = "";
		var queryObj = "";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {

			sql = "SELECT t_student.stu_id 
					FROM t_student 
					INNER JOIN t_class ON t_class.cls_id = t_student.cls_id 
					WHERE 
						t_class.grade= :grade 
						AND 
						t_class.sbj_id = :subjectId 
					ORDER BY 
						t_student.stu_id ";

			queryObj = new Query( datasource=application.dnsMaster);
			queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
			
			var rs_student = queryObj.execute( sql=sql ).getResult();
			
			for ( var a=1; a LTE rs_student.recordCount; a++ ) {
				
				var studentId = rs_student["stu_id"][a];
				
				/* 刷新指定学生的学分统计数据 */
				creditFactory.creditRefresh( studentId ); 

			}
		
			/* 所有学生绩点统计完成后, 刷新一次排名信息 */
			
			/* 计算 专业-年级 必修课排名 */
			sql = "SELECT 
					t_student_profile.stu_id, t_student_profile.credit_req_avgpt 
					FROM t_student_profile
					INNER JOIN t_student ON t_student.stu_id = t_student_profile.stu_id
					INNER JOIN t_class ON t_class.cls_id = t_student.cls_id  
					WHERE 
						t_class.grade = :grade 
						AND 
						t_class.sbj_id = :subjectId 
					ORDER BY 
						t_student_profile.credit_req_avgpt DESC";

			queryObj = new Query( datasource=application.dnsMaster);
			
			queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
				
			var rs_rank = queryObj.execute( sql=sql ).getResult();
			
			var subjectIndex = 0; /* 名次 */
			var subjectRank = 0; /* 排名百分比 */
			
			for ( var c=1; c LTE rs_rank.recordCount; c++ ) {
				
				var targetStudentId = rs_rank["stu_id"][c];
				
				subjectIndex = c;
				subjectRank = numberFormat( 100 * subjectIndex / rs_rank.recordCount, "_.__");
				
				/* 专业排名 要考虑必修课平均绩点并列的情况 */
				if ( c gt 1 ) {
					
					if ( rs_rank["credit_req_avgpt"][c] eq rs_rank["credit_req_avgpt"][c-1] ) {
						
						subjectIndex = c-1;
						subjectRank = numberFormat( 100 * subjectIndex / rs_rank.recordCount, "_.__");
						
					}
					
				}
				
				/* 专业排名 三名并列 */
				if ( c gt 2 ) {
					
					if ( rs_rank["credit_req_avgpt"][c] eq rs_rank["credit_req_avgpt"][c-2] ) {
						
						subjectIndex = c-2;
						subjectRank = numberFormat( 100 * subjectIndex / rs_rank.recordCount, "_.__");
						
					}
					
				}
				
				/* 专业排名 四名并列 */
				if ( c gt 3 ) {
					
					if ( rs_rank["credit_req_avgpt"][c] eq rs_rank["credit_req_avgpt"][c-3] ) {
						
						subjectIndex = c-3;
						subjectRank = numberFormat( 100 * subjectIndex / rs_rank.recordCount, "_.__");
						
					}
					
				}

				sql = "UPDATE t_student_profile 
						SET 
							subject_rank = :subjectRank, 
							subject_idx = :subjectIndex 
						WHERE 
							stu_id = :targetStudentId";
				
				queryObj = new Query(datasource = application.dnsMaster);
					
				queryObj.addParam( name="targetStudentId", value=targetStudentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="subjectRank", value=subjectRank, cfsqltype="cf_sql_decimal", scale=2 );
				queryObj.addParam( name="subjectIndex", value=subjectIndex, cfsqltype="cf_sql_integer" );
					
				queryObj.execute( sql=sql );
				
			}
			
			
			/* 计算 专业-年级 全部课程排名 */
			sql = "SELECT 
					t_student_profile.stu_id, t_student_profile.credit_avgpt 
					FROM t_student_profile
					INNER JOIN t_student ON t_student.stu_id = t_student_profile.stu_id
					INNER JOIN t_class ON t_class.cls_id = t_student.cls_id  
					WHERE 
						t_class.grade = :grade 
						AND 
						t_class.sbj_id = :subjectId 
					ORDER BY 
						t_student_profile.credit_avgpt DESC";

			queryObj = new Query( datasource=application.dnsMaster);
			
			queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
				
			var rs_rank_all = queryObj.execute( sql=sql ).getResult();
			
			
			var subjectIndexAll = 0; /* 名次 */
			var subjectRankAll = 0; /* 排名百分比 */
			
			for ( var d=1; d LTE rs_rank_all.recordCount; d++ ) {
				
				var targetAllStudentId = rs_rank_all["stu_id"][d];
				
				subjectIndexAll = d;
				subjectRankAll = numberFormat( 100 * subjectIndexAll / rs_rank_all.recordCount, "_.__");
				
				/* 专业排名 要考虑必修课平均绩点并列的情况 */
				if ( d gt 1 ) {
					
					if ( rs_rank_all["credit_avgpt"][d] eq rs_rank_all["credit_avgpt"][d-1] ) {
						
						subjectIndexAll = d-1;
						subjectRankAll = numberFormat( 100 * subjectIndexAll / rs_rank_all.recordCount, "_.__");
						
					}
					
				}
				
				/* 专业排名 三名并列 */
				if ( d gt 2 ) {
					
					if ( rs_rank_all["credit_avgpt"][d] eq rs_rank_all["credit_avgpt"][d-2] ) {
						
						subjectIndexAll = d-2;
						subjectRankAll = numberFormat( 100 * subjectIndexAll / rs_rank_all.recordCount, "_.__");
						
					}
					
				}
				
				/* 专业排名 四名并列 */
				if ( d gt 3 ) {
					
					if ( rs_rank_all["credit_avgpt"][d] eq rs_rank_all["credit_avgpt"][d-3] ) {
						
						subjectIndexAll = d-3;
						subjectRankAll = numberFormat( 100 * subjectIndexAll / rs_rank_all.recordCount, "_.__");
						
					}
					
				}

				sql = "UPDATE t_student_profile 
						SET 
							subject_rank_all = :subjectRankAll, 
							subject_idx_all = :subjectIndexAll 
						WHERE 
							stu_id = :targetAllStudentId";
				
				queryObj = new Query(datasource = application.dnsMaster);
					
				queryObj.addParam( name="targetAllStudentId", value=targetAllStudentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="subjectRankAll", value=subjectRankAll, cfsqltype="cf_sql_decimal", scale=2 );
				queryObj.addParam( name="subjectIndexAll", value=subjectIndexAll, cfsqltype="cf_sql_integer" );
					
				queryObj.execute( sql=sql );
				
			}
			

		}

	}
	
	/**
	* @hint "创建专业方向信息"
	* 
	* @event "MachII Event Object"
	*/
	public void function createSubjectDirection( required MachII.framework.Event event ) output=false {
 
		var sequenceSubject = getsequenceSubject();
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
		 
			var sql = "SELECT a.schooling_length,a.subject_code,
							   a.term_start,a.level_code,
							   a.institute_id,a.b_id,
							   a.sbj_id 
						FROM t_subject a 
						WHERE a.sbj_id = :SubjectID ";
			var queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="SubjectID", value=event.getArg("SubjectID"), cfsqltype="cf_sql_varchar" );
			var rs_subject = queryObj.execute( sql=sql ).getResult();
		 
			
			/* 继承专业相关信息 */
			sequenceSubject.setsubjectID(event.getArg("SubjectID"));
			var sbjID = sequenceSubject.getDirID();
			sql = "INSERT INTO t_subject a (
						   a.sbj_name,
						   a.sbj_short,
						   a.sbj_english,
						   a.schooling_length,
						   a.subject_code,
						   a.term_start,
						   a.level_code,
						   a.institute_id,
						   a.b_id,
						   a.sbj_id,
						   a.sbj_direction )
					VALUES (
						   :SubjectName ,
						   :SubjectShortName ,
						   :SubjectEnglish ,
						   :SchoolingLength ,
						   :SubjectCode ,
						   :TermStart ,
						   :StudentLevel ,
						   :Institute ,
						   :BranchID ,
						   :SubjectID ,
						   :SubjectDirection 
						   
					)";
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="SubjectName", value=event.getArg("DirectionName"), cfsqltype="cf_sql_varchar" );	
			queryObj.addParam( name="SubjectShortName", value=event.getArg("DirectionShortName"), cfsqltype="cf_sql_varchar" );	
			queryObj.addParam( name="SubjectEnglish", value=event.getArg("DirectionEnglish"), cfsqltype="cf_sql_varchar" );	
			queryObj.addParam( name="SchoolingLength", value=rs_subject.schooling_length, cfsqltype="cf_sql_varchar" );	
			queryObj.addParam( name="SubjectCode", value=rs_subject.subject_code, cfsqltype="cf_sql_varchar" );	
			queryObj.addParam( name="TermStart", value=rs_subject.term_start, cfsqltype="cf_sql_char" );	
			queryObj.addParam( name="StudentLevel", value=rs_subject.level_code, cfsqltype="cf_sql_char" );	
			queryObj.addParam( name="Institute", value=rs_subject.institute_id, cfsqltype="cf_sql_varchar" );	
			queryObj.addParam( name="BranchID", value=rs_subject.b_id, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="SubjectID", value=sbjID, cfsqltype="cf_sql_varchar" );	
			queryObj.addParam( name="SubjectDirection", value=right(sbjID, 1), cfsqltype="cf_sql_char" );	
		 
			queryObj.execute( sql=sql );
			
			
			event.setArg("SubID", event.getArg("SubjectID"));
			event.setArg("DepID", rs_subject.institute_id);
			event.setArg("TabID", "labDirection");
			
		}
		
	}
	
	/**
	* @hint "删除专业及相关信息"
	* 
	* @event "MachII Event Object"
	*/
	public void function deleteSubject( required MachII.framework.Event event ) output=false {
		
		var depId = event.getArg("InstituteID");
		var subjectId = event.getArg("SubjectID");
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
		
			var sql = "SELECT a.sbj_id 
						FROM t_subject a 
						WHERE a.sbj_id = :subjectId 
							  OR
							  a.sbj_id LIKE :subjectDir ";
			var queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="subjectDir", value=subjectId & "_", cfsqltype="cf_sql_varchar" );
			var rs_subject = queryObj.execute( sql=sql ).getResult();
			
			for (var a=1; a LTE rs_subject.recordCount; a++  ) {
				
				sql = "DELETE FROM t_subject 
							WHERE sbj_id = :subjectId ";
				queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="subjectId", value=rs_subject["sbj_id"][a], cfsqltype="cf_sql_char" );
				queryObj.execute( sql=sql );
			}
			
			event.setArg("DepID", depId);
			
		}

	}

	/**
	* @hint "更新专业信息"
	* 
	* @event "MachII Event Object"
	*/
	public void function updateSubject( required MachII.framework.Event event ) output=false {
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			sbjCode = left(event.getArg("SubjectCode"), 6);
			var sql = " SELECT a.subject_code 
						FROM t_dict_china_subject a 
						WHERE a.subject_code = :sbjCode ";
			var queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="sbjCode", value=sbjCode, cfsqltype="cf_sql_char" );
			var rs_dictSbj = queryObj.execute( sql=sql ).getResult();
			if (rs_dictSbj.recordCount EQ 0) {
				var sql = "INSERT INTO t_dict_china_subject (
								   subject_code,
								   subject_name 
							)
							VALUES (
								  :SubjectCode ,
								  :SubjectName  
							)";
				var queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="SubjectCode", value=sbjCode, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="SubjectName", value=event.getArg("SubjectName"), cfsqltype="cf_sql_varchar" );
				queryObj.execute( sql=sql );
				
			}
			sql = "SELECT a.sbj_id 
						FROM t_subject a 
						WHERE a.sbj_id = :SubjectID ";
			queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="SubjectID", value=event.getArg("SubjectID"), cfsqltype="cf_sql_varchar" );
			var rs_subject = queryObj.execute( sql=sql ).getResult();
			
			if (rs_subject.recordCount GT 0) {
				
				var sql = "UPDATE t_subject a 
								   SET
								   a.sbj_name = :SubjectName ,
								   a.sbj_short = :SubjectShortName ,
								   a.sbj_english = :SubjectEnglish ,
								   a.sbj_direction = :SubjectDirection ,
								   a.schooling_length = :SchoolingLength ,
								   a.subject_code = :SubjectCode ,
								   a.term_start = :TermStart ,
								   a.level_code = :StudentLevel ,
								   a.institute_id = :InstituteID ,
								   a.b_id = :BranchID 
							WHERE a.sbj_id = :SubjectID ";
					var updateObj = new Query(datasource = application.dnsMaster);
					updateObj.addParam(name ="SubjectName", value=event.getArg("SubjectName"), cfsqltype="cf_sql_varchar");
					updateObj.addParam(name ="SubjectShortName", value=event.getArg("SubjectShortName"), cfsqltype="cf_sql_varchar");
					updateObj.addParam(name ="SubjectEnglish", value=event.getArg("SubjectEnglish"), cfsqltype="cf_sql_varchar");
					updateObj.addParam(name ="SubjectDirection", value="0", cfsqltype="cf_sql_char");
					updateObj.addParam(name ="SchoolingLength", value=event.getArg("SchoolingLength"), cfsqltype="cf_sql_varchar");							
					updateObj.addParam(name ="SubjectCode", value=event.getArg("SubjectCode"), cfsqltype="cf_sql_varchar");	
					updateObj.addParam(name ="TermStart", value=event.getArg("TermStart"), cfsqltype="cf_sql_char");
					updateObj.addParam(name ="StudentLevel", value=event.getArg("StudentLevel"), cfsqltype="cf_sql_char");
					updateObj.addParam(name ="InstituteID", value=event.getArg("InstituteID"), cfsqltype="cf_sql_varchar");
					updateObj.addParam(name ="BranchID", value=event.getArg("LearningBranch"), cfsqltype="cf_sql_char");
					updateObj.addParam(name ="SubjectID", value=event.getArg("SubjectID"), cfsqltype="cf_sql_varchar");	
					updateObj.execute(sql = sql);
			}
			
			sql = "SELECT a.sbj_id 
						FROM t_subject a 
						WHERE a.sbj_id = :SubjectID ";
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="SubjectID", value=event.getArg("SubjectID"), cfsqltype="cf_sql_varchar" );
			var rs_subjects = queryObj.execute( sql=sql ).getResult();
			sql = "SELECT a.institute_id 
						FROM t_institute a 
						WHERE a.institute_id = :InstituteID ";
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="InstituteID", value=event.getArg("InstituteID"), cfsqltype="cf_sql_varchar" );
			var rs_department = queryObj.execute( sql=sql ).getResult();
			
			event.setArg("SubID",rs_subjects.sbj_id);
			event.setArg("DepID", rs_department.institute_id);
			event.setArg("TabID", "labBaseInfo");
			
		}
	}
	
	/**
	* @hint "创建专业信息"
	* 
	* @event "MachII Event Object"
	*/
	public void function createSubject( required MachII.framework.Event event ) output=false {
		
		var department = "";
		var branch = "";
		var subject = "";
		
		var sbjCode = "";
		var dictSbj = "";
		var sequenceSubject = getsequenceSubject();
		var subjectId = "";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {

			sbjCode = left(event.getArg("SubjectCode"), 6);

			var sql = "SELECT 1
						FROM t_dict_china_subject a 
						WHERE a.subject_code = :sbjCode";
			var queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="sbjCode", value=sbjCode, cfsqltype="cf_sql_char" );
			var rs_subject = queryObj.execute( sql=sql ).getResult();
			
			if (rs_subject.recordCount EQ 0) {
				sql = "INSERT INTO t_dict_china_subject a
						(
							   subject_code,
							   subject_name
						)VALUES(
								:SubjectCode ,
								:SubjectName 
						)";
				queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="SubjectCode", value=sbjCode, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="SubjectName", value=event.getArg("SubjectName"), cfsqltype="cf_sql_varchar" );
				queryObj.execute( sql=sql );
			}
			sequenceSubject.setTermStart(event.getArg("TermStart"));
			sequenceSubject.setStudentLevel(event.getArg("StudentLevel"));
			subjectId = sequenceSubject.getID();
			
			sql = "INSERT INTO t_subject(
						   sbj_name,
						   sbj_short,
						   sbj_english,
						   sbj_direction,
						   schooling_length,
						   subject_code,
						   term_start,
						   level_code,
						   institute_id,
						   b_id,
						   sbj_id 
					)VALUES(
							:SubjectName ,
							:SubjectShortName ,
							:SubjectEnglish ,
							:SubjectDirection ,
							:SchoolingLength ,
							:SubjectCode ,
							:TermStart ,
							:StudentLevel ,
							:Institute ,
							:LearningBranch ,
							:SubjectID 
					)";
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="SubjectName", value=event.getArg("SubjectName"), cfsqltype="cf_sql_char" );
			queryObj.addParam( name="SubjectShortName", value=event.getArg("SubjectShortName"), cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="SubjectEnglish", value=event.getArg("SubjectEnglish"), cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="SubjectDirection", value="0", cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="SchoolingLength", value=event.getArg("SchoolingLength"), cfsqltype="cf_sql_char" );
			queryObj.addParam( name="SubjectCode", value=event.getArg("SubjectCode"), cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="TermStart", value=event.getArg("TermStart"), cfsqltype="cf_sql_char" );
			queryObj.addParam( name="StudentLevel", value=event.getArg("StudentLevel"), cfsqltype="cf_sql_char" );
			queryObj.addParam( name="Institute", value=event.getArg("InstituteID"), cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="LearningBranch", value=event.getArg("LearningBranch"), cfsqltype="cf_sql_char" );
			queryObj.addParam( name="SubjectID", value=subjectId, cfsqltype="cf_sql_varchar" );
			queryObj.execute( sql=sql );
			
			event.setArg("DepID", event.getArg("InstituteID"));
			event.setArg("TabID", "labSubject");
			
		}
		
	}
	
}

</cfscript>